package com.kai.steering.data.service.util;

import com.alibaba.fastjson.JSON;
import com.kai.steering.common.util.JsonUtils;
import com.kai.steering.exception.ExcelException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OfficeUtil {
    /**
     * 获取整个文件
     *
     * @param file 文件
     * @return
     */
    static public List<List> excelReaderFile(InputStream file) {
        ArrayList<List> fileS = new ArrayList<>();//文件

        try (
                Workbook workbook = new XSSFWorkbook(file)) {
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i); // 获取个工作表
                ArrayList<List> surfaceS = new ArrayList<>();//表

                for (int j = 0; j < sheet.getLastRowNum(); j++) {
                    ArrayList<String> lineS = new ArrayList<>();//行
                    Row row = sheet.getRow(j);

                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        Cell cell = row.getCell(k);
                        switch (cell.getCellType()) { // 根据单元格类型获取值
                            case STRING:
                                lineS.add(cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                lineS.add(cell.getNumericCellValue() + "");
                                break;
                            case BOOLEAN:
                                lineS.add(cell.getNumericCellValue() + "");
                                break;
                            default:
                                lineS.add("");
                        }
                    }
                    surfaceS.add(lineS);
                }
                fileS.add(surfaceS);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return fileS;
    }

    /**
     * 获取表
     *
     * @param file 文件
     * @param page 表
     * @return
     */
    static public List<List> excelReaderPage(InputStream file, int page) {
        ArrayList<List> ePage = new ArrayList<>();//表

        try (
            Workbook workbook = new XSSFWorkbook(file)) {
            Sheet sheet = workbook.getSheetAt(page); // 获取工作表
            for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
                Row row = sheet.getRow(j);
                ArrayList<String> eLine = new ArrayList<>();//行

                for (int k = 0; k < row.getLastCellNum(); k++) {
                    Cell cell = row.getCell(k);
                    if (cell==null){
                        eLine.add("");
                        continue;
                    }
                    switch (cell.getCellType()) { // 根据单元格类型获取值
                        case STRING:
                            eLine.add(cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            eLine.add(cell.getNumericCellValue() + "");
                            break;
                        case BOOLEAN:
                            eLine.add(cell.getNumericCellValue() + "");
                            break;
                        default:
                            eLine.add("");
                    }
                }
                ePage.add(eLine);
            }
        } catch (IOException e) {

            e.printStackTrace();
        }
        return ePage;

    }

    /**
     * 获取行
     *
     * @param file 文件
     * @param page 表
     * @param line 行
     * @return
     */
    static public List<String> excelReaderLine(InputStream file, int page, int line) {
        ArrayList<String> eLine = new ArrayList<>();//行

        try (
                Workbook workbook = new XSSFWorkbook(file)) {
            Sheet sheet = workbook.getSheetAt(page); // 获取个工作表
            Row row = sheet.getRow(line);
            for (Cell cell : row) { // 遍历单元格

                switch (cell.getCellType()) { // 根据单元格类型获取值
                    case STRING:
                        eLine.add(cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        eLine.add(cell.getNumericCellValue() + "");
                        break;
                    case BOOLEAN:
                        eLine.add(cell.getNumericCellValue() + "");
                        break;
                    default:
                        eLine.add("");
                }
            }

        } catch (IOException e) {

            e.printStackTrace();
        }
        return eLine;
    }

    /**
     * 获取单元格
     *
     * @param file   文件
     * @param page   表
     * @param line   行
     * @param column 列
     * @return
     */
    static public String excelReaderLattice(InputStream file, int page, int line, int column) {
        String lattice = new String();//行

        try (
                Workbook workbook = new XSSFWorkbook(file)) {
            Sheet sheet = workbook.getSheetAt(page); // 获取个工作表
            Row row = sheet.getRow(line);
            Cell cell = row.getCell(column);


            switch (cell.getCellType()) { // 根据单元格类型获取值
                case STRING:
                    lattice = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    lattice = cell.getNumericCellValue() + "";
                    break;
                case BOOLEAN:
                    lattice = cell.getNumericCellValue() + "";
                    break;
                default:
                    lattice = "";
            }

        } catch (IOException e) {

            e.printStackTrace();
        }
        return lattice;
    }

    /**
     * 修改
     */
    static public ByteArrayOutputStream excelChangeLattice(InputStream file, List<ExcelException> excelExceptions) throws IOException {
        HashMap<Integer, Sheet> pageMap = new HashMap<>();
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        Workbook workbook = new XSSFWorkbook(file);

        for (ExcelException excelException : excelExceptions) {
            Sheet page = null;
            page = pageMap.get(excelException.getPage());
            if (page == null) {
                page = workbook.getSheetAt(excelException.getPage()); // 获取个工作表
                pageMap.put(excelException.getPage(), page);
            }
            Row line = page.getRow(excelException.getLine());
            Cell cell_0 = line.getCell(0);
            // 创建CellStyle并设置填充颜色
            CellStyle cellStyle_0 = workbook.createCellStyle();
            cellStyle_0.setFillForegroundColor(IndexedColors.RED.getIndex()); // 使用预定义颜色
            cellStyle_0.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充模式
            // 将CellStyle应用到单元格
            cell_0.setCellStyle(cellStyle_0);
            Cell cell_7 = line.getCell(7);
            if (cell_7 == null) {
                // 单元格不存在，创建它
                cell_7 = line.createCell(7);
            }

            // 创建CellStyle并设置填充颜色

            CellStyle cellStyle_7 = workbook.createCellStyle();
            // 将CellStyle应用到单元格
            cell_7.setCellStyle(cellStyle_7);

            cell_7.setCellValue(JSON.toJSONString(excelException.getInformation().getMessage()));//设置文字

        }
        workbook.write(outputStream);

        return outputStream;
    }


/*
    public static void main(String[] args) {
        String filePath = "E:\\Excel文件读取测试.xlsx"; // 替换为你的Excel文件路径
        File file = new File(filePath);
        List<List> eFile = excelReaderFile(file);
        List<List> ePage1 = excelReaderPage(file, 0);
        List<List> ePage2 = excelReaderPage(file, 1);
        List<String> eLine1 = excelReaderLine(file, 1, 2);
        List<String> eLine2 = excelReaderLine(file, 1, 2);
        String eLattice = excelReaderLattice(file, 1, 2, 2);

        System.out.println("eFile________________________________________________________");
        System.out.println(JsonUtils.toJsonString(eFile));

        System.out.println("ePage1________________________________________________________");
        System.out.println(JsonUtils.toJsonString(ePage1));

        System.out.println("ePage2________________________________________________________");
        System.out.println(JsonUtils.toJsonString(ePage2));
        System.out.println("eLine1________________________________________________________");
        System.out.println(JsonUtils.toJsonString(eLine1));

        System.out.println("eLine2________________________________________________________");
        System.out.println(JsonUtils.toJsonString(eLine2));

        System.out.println("eLattice________________________________________________________");
        System.out.println(JsonUtils.toJsonString(eLattice));
        System.out.println("________________________________________________________");



    }
*/
}
